package cmsoa.db;


import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.struts2.ServletActionContext;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.ResultTransformer;

import cmsoa.util.ToUtils;


public class DBUtil {
	public final static String DB_SESSION_NAME = "REQUEST_DB_UTIL_NUMBER_0033";
	public final static MapResultTransformer mapResultTransformer = new MapResultTransformer();
	public final static ArrayResultTransformer arrayResultTransformer = new ArrayResultTransformer();
	public final static ListResultTransformer listResultTransformer = new ListResultTransformer();
	private Session session;
	private Transaction tran;
	private ResultTransformer resultTransformer;
	
	public static void main(String[] args) throws  Exception {
		DBUtil db = new DBUtil();
	}
	
	public List queryByPojo(Class clas , String where , Map params, String orderby){
        StringBuffer temp = new StringBuffer("from ").append(clas.getName()).append(" as tmp where 1=1");
        temp.append(where==null?"":where);
        Query query = this.session.createQuery(temp.toString());
        if(params!=null && params.size()>0){
            Iterator iter = params.keySet().iterator();
            while(iter.hasNext()){
                String key = iter.next().toString();
                Object value = params.get(key);
                query.setParameter(key, value);
            }
        }
        temp.append(orderby==null?"":" order by "+orderby);
        return query.list();
    }
	
	public DBUtil(){
		this.init();
	}
	
	public List queryByPojo(Class clas){
		return queryByPojo(clas, null, null);
	}
	
	public List queryByPojo(Class clas , Map params){
		return queryByPojo(clas, params, null);
	}
	
	public List queryByPojo(Class clas , Map params, String orderby){
		StringBuffer temp = new StringBuffer("from ").append(clas.getName()).append(" as tmp where 1=1");
		
		List paramList = buildParams(temp, params);
		buildOrderby(temp, orderby);//排序参数
		
		//创建查询，并执行
		Query query = this.session.createQuery(temp.toString());
		for (int i = 0; i < paramList.size(); i++) {
			query.setParameter(i, paramList.get(i));
		}
		
		return query.list();
	}
	
	/**
	 * 构建参数，拼接到sql语句，同时返回参数集合
	 * @param sql 语句
	 * @param params 参数
	 * @return
	 */
	private static List buildParams(StringBuffer sql, Map params){
		List paramList = new ArrayList();
		if(params!=null && params.size()>0){
			Iterator iter = params.keySet().iterator();
			Object key,value;
			while(iter.hasNext()){
				key = iter.next();
				value = params.get(key);
				sql.append(" and tmp.");
				sql.append(key);
				if(value!=null){
					sql.append("=? ");
					paramList.add(value);
				}
			}
		}
		return paramList;
	}
	
	/**
	 * 为sql语句子追加or derby
	 * @param sql
	 * @param orderby
	 */
	private static void buildOrderby(StringBuffer sql, String orderby){
		if(orderby!=null && !orderby.equals("")){
			sql.append(" order by ");
			sql.append(orderby);
		}
	}
	
	/**
	 * 执行sql语句
	 * @param sql 语句
	 * @param params 参数
	 * @return
	 */
	public List queryBySQL(String sql, Object... params){
		List plist = new ArrayList(params.length);
		if(params!=null && params.length>0){
			for (int i = 0; i < params.length; i++) {
				plist.add(params[i]);
			}
		}
		return queryBySQL(sql, plist);
	}
	
	/**
	 * 查询总行数(自动替换Sql语句)
	 * @param sql 语句
	 * @param params 参数
	 * @return
	 */
	public int queryCountBySQL(String sql, List params){
		sql = sql.toUpperCase();
		int startIndex = sql.indexOf("FROM");
		sql = "SELECT COUNT(1) ROW_COUNT "+sql.subSequence(startIndex, sql.length());
		List list = queryBySQL(sql, params, 0, 0);
		return Integer.parseInt(((Map)list.get(0)).get("rowCount").toString());
	}
	
	/**
	 * 执行sql语句
	 * @param sql 语句 
	 * @param params 参数
	 * @return
	 */
	public List queryBySQL(String sql, List params){
		return queryBySQL(sql, params, 0, 0);
	}
	
	/**
	 * 执行sql语句，并分页查询
	 * @param sql 语句
	 * @param params 参数
	 * @param firstResult 起始行数
	 * @param maxResults 最大行数
	 * @return
	 */
	public List queryBySQL(String sql, List params , int firstResult, int maxResults){
		Query query = this.session.createSQLQuery(sql);
		if(params!=null && params.size()>0){
			for (int i = 0; i < params.size(); i++) {
				query.setParameter(i, params.get(i));
			}
		}
		if(maxResults>0){
			query.setFirstResult(firstResult);
			query.setMaxResults(maxResults);
		}
		query.setResultTransformer(resultTransformer==null?mapResultTransformer:resultTransformer);
		return query.list();
	}
	
	/**
	 * 查询sql语句 ，按循map填充参数， key作为列名
	 * @param sql 
	 * @param params
	 * @return
	 */
	public List queryBySQL(String sql, Map params){
		StringBuffer temp = new StringBuffer(sql);
		List paramList = new ArrayList();
		if(params!=null && params.size()>0){
			Iterator iter = params.keySet().iterator();
			Object key,value;
			while(iter.hasNext()){
				key = iter.next();
				value = params.get(key);
				if(value!=null){
					temp.append(" ");
					temp.append(key);
					temp.append("=? ");
					paramList.add(value);
				}
			}
		}
		return this.queryBySQL(temp.toString(), paramList.toArray());
	}
	
	/**
	 * 执行SQL语句，按循map填充参数， key作为列名
	 * @param sql
	 * @param params
	 * @return
	 */
	public int executeSQL(String sql, Map params){
		StringBuffer temp = new StringBuffer(sql);
		List paramList = new ArrayList();
		if(params!=null && params.size()>0){
			Iterator iter = params.keySet().iterator();
			Object key,value;
			while(iter.hasNext()){
				key = iter.next();
				value = params.get(key);
				if(value!=null){
					temp.append(" ");
					temp.append(key);
					temp.append("=? ");
					paramList.add(value);
				}
			}
		}
		return this.executeSQL(temp.toString(), paramList.toArray());
	}
	
	/**
	 * 执行SQL语句，按循序填充参数
	 * @param sql sql语句
	 * @param params 参数
	 * @return 影响行数
	 */
	public int executeSQL(String sql, Object... params){
		this.beginTransaction();
		Query query = this.session.createSQLQuery(sql);
		int paramIndex = 0;
		if(params!=null && params.length>0){
			for (int i = 0; i < params.length; i++) {
				if(params[i]!=null){
					query.setParameter(paramIndex++, params[i]);
				}
			}
		}
		return query.executeUpdate();
	}
	
	/**
	 * 插入一个pojo
	 * @param pojo对象
	 */
	public void insert(Object pojo){
		this.beginTransaction();
		this.session.save(pojo);
	}
	
	/**
	 * 保存或更新一个pojo
	 * @param pojo对象
	 */
	public void saveOrUpdate(Object pojo){
		this.beginTransaction();
		this.session.saveOrUpdate(pojo);
	}
	
	/**
	 * 更新一个pojo
	 * @param pojo对象
	 */
	public void update(Object pojo){
		this.beginTransaction();
		this.session.update(pojo);
	}
	
	/**
	 * 删除一个Pojo
	 * @param pojo对象
	 */
	public void delete(Object pojo){
		this.beginTransaction();
		this.session.delete(pojo);
	}
	
	/**
	 * 初始化会话 
	 */
	public void init(){
		if(this.session!=null && this.session.isOpen()){
			this.close();
		}
		this.session = HibernateSessionFactory.getSession();
	}
	
	/**
	 * 为当前会话启动事务
	 */
	public void beginTransaction(){
		if(this.tran == null || !this.tran.isActive()){
			this.tran = this.session.beginTransaction();
		}
	}
	
	/**
	 * 回滚当前会话
	 */
	public void rollback(){
		if(this.tran!=null && this.tran.isActive()){
			this.tran.rollback();
		}
	}
	
	/**
	 * 提交当前会话
	 */
	public void commit(){
		if(this.tran!=null && this.tran.isActive()){
			this.tran.commit();
		}
	}
	
	/**
	 * 关闭当前会话
	 */
	public void close(){
		this.commit();
		if(this.session!=null && this.session.isOpen()){
			this.session.close();
		}
		this.tran = null;
		this.session = null;
	}
	
	/**
	 * 获取Hibernate Session
	 * @return
	 */
	public Session getSession(){
		return this.session;
	}
	
	/**
	 * <p>获取在请求中存放的数据库工具类<p>
	 * @return
	 */
	public static DBUtil getDBUtilByRequest(){
		HttpServletRequest request = ServletActionContext.getRequest();
		if(request.getAttribute(DB_SESSION_NAME) == null){
			request.setAttribute(DB_SESSION_NAME, new DBUtil());
		}
		return (DBUtil)request.getAttribute(DB_SESSION_NAME);
	}

	public ResultTransformer getResultTransformer() {
		return resultTransformer;
	}

	public void setResultTransformer(ResultTransformer resultTransformer) {
		this.resultTransformer = resultTransformer;
	}
	
	public void setResultTransformer(String resultTransformerName) {
		
	}
}

class ListResultTransformer implements ResultTransformer{
	@Override
	public List transformList(List arg0) {
		return arg0;
	}
	@Override
	public Object transformTuple(Object[] values, String[] keys) {
		List list = new ArrayList(values.length);
		for (int i = 0; i < values.length; i++) {
			list.add(values[i]);
		}
		return list;
	}
}

class ArrayResultTransformer implements ResultTransformer{
	@Override
	public List transformList(List arg0) {
		return arg0;
	}
	@Override
	public Object transformTuple(Object[] values, String[] keys) {
		Object[] array = new Object[values.length];
		for (int i = 0; i < values.length; i++) {
			array[i] = values[i];
		}
		return array;
	}
}

class MapResultTransformer implements ResultTransformer{
	@Override
	public List transformList(List arg0) {
		return arg0;
	}
	@Override
	public Object transformTuple(Object[] values, String[] keys) {
		Map map = new HashMap(values.length);
		for (int i = 0; i < keys.length; i++) {
			map.put(ToUtils.dbMapKeyToBeanFieldName(keys[i]), values[i]);
		}
		return map;
	}
}